/*
 * $RCSfile: SqlPlus.java,v $$
 * $Revision: 1.1 $
 * $Date: 2013-3-19 $
 *
 * Copyright (C) 2008 Skin, Inc. All rights reserved.
 *
 * This software is the proprietary information of Skin, Inc.
 * Use is subject to license terms.
 */
package com.skin.webcat.database.sql;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.Reader;
import java.io.StringReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.skin.webcat.util.Jdbc;
import com.skin.webcat.database.Column;

/**
 * <p>Title: SqlPlus</p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2006</p>
 * @author xuesong.net
 * @version 1.0
 */
public class SqlPlus {
    private static final int CREATE = 1;
    private static final int INSERT = 2;
    private static final int UPDATE = 3;
    private static final int DELETE = 4;
    private static final int SELECT = 5;
    private static final int ALTER  = 6;
    private static final int DROP   = 7;
    private static final int SHOW   = 8;
    private static final int UNKNOWN = 99;
    private static final Logger logger = LoggerFactory.getLogger(SqlPlus.class);

    /**
     * @param connection
     * @param sql
     * @return SqlResult
     */
    public static SqlResult execute(Connection connection, String sql) {
        return execute(connection, new StringReader(sql));
    }

    /**
     * @param connection
     * @param reader
     * @return SqlResult
     */
    public static SqlResult execute(Connection connection, Reader reader) {
        int status = 200;
        StringBuilder result = new StringBuilder();
        Statement statement = null;

        try {
            BufferedReader bufferedReader = null;

            if(reader instanceof BufferedReader) {
                bufferedReader = (BufferedReader)reader;
            }
            else {
                bufferedReader = new BufferedReader(reader);
            }

            String sql = null;
            statement = connection.createStatement();

            while((sql = getSql(bufferedReader)) != null) {
                int type = getSqlType(sql);
                logger.info("type: {}, sql: {}", type, sql);

                if(type == SELECT) {
                    return select(connection, sql);
                }

                if(type == SHOW) {
                    return select(connection, sql);
                }

                try {
                    long t1 = System.currentTimeMillis();
                    int count = statement.executeUpdate(sql);
                    long t2 = System.currentTimeMillis();

                    result.append("[SQL]: ");
                    result.append(sql);
                    result.append("\r\n");
                    result.append("Affected rows: ");
                    result.append(count);
                    result.append("\r\n");
                    result.append("Time: ");
                    result.append(t2 - t1);
                    result.append("ms\r\n\r\n");
                }
                catch(SQLException e) {
                    logger.error(e.getMessage(), e);
                    status = 500;
                    result.append("[SQL]: ");
                    result.append(sql);
                    result.append("\r\n\r\n");
                    result.append(e.getMessage());
                    result.append("\r\n\r\n");
                    Jdbc.rollback(connection);
                    break;
                }
            }
            Jdbc.commit(connection);
        }
        catch(Exception e) {
            logger.error(e.getMessage(), e);
            status = 500;
            result.append("[ERROR]: ");
            result.append(e.getMessage());
            result.append("\r\n\r\n");
            Jdbc.rollback(connection);
        }
        finally {
            Jdbc.close(statement);
        }
        return new SqlResult(status, result.toString());
    }

    /**
     * @param bufferedReader
     * @return String
     * @throws IOException
     */
    public static String getSql(BufferedReader bufferedReader) throws IOException {
        String line = null;
        StringBuilder buffer = new StringBuilder();

        while((line = bufferedReader.readLine()) != null) {
            line = line.trim();

            if(line.length() < 1 || line.startsWith("--")) {
                continue;
            }

            if(line.endsWith(";")) {
                buffer.append(line.substring(0, line.length() - 1));
                buffer.append(" ");
                break;
            }
            else {
                buffer.append(line);
                buffer.append(" ");
            }
        }
        return (buffer.length() > 0 ? buffer.toString() : null);
    }

    /**
     * @param connection
     * @param sql
     * @return SqlResult
     * @throws SQLException 
     */
    public static SqlResult select(Connection connection, String sql) throws SQLException {
        Statement statement = null;
        ResultSet resultSet = null;        

        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);

            ResultSetMetaData medaData = resultSet.getMetaData();
            List<Column> columns = getColumns(medaData);
            List<Record> records = getRecords(resultSet, 200);
            SqlResult sqlResult = SqlResult.success("success");
            sqlResult.setColumns(columns);
            sqlResult.setRecords(records);
            return sqlResult;
        }
        finally {
            Jdbc.close(resultSet);
            Jdbc.close(statement);
        }
    }

    /**
     * @param connection
     * @param sql
     * @return RunResult
     * @throws SQLException
     */
    public static int update(Connection connection, String sql) throws SQLException {
        Statement statement = null;

        try {
            statement = connection.createStatement();
            return statement.executeUpdate(sql);
        }
        finally {
            Jdbc.close(statement);
        }
    }

    /**
     * @param medaData
     * @return List<Column>
     * @throws SQLException
     */
    private static List<Column> getColumns(ResultSetMetaData medaData) throws SQLException {
        int columnCount = medaData.getColumnCount();
        List<Column> columns = new ArrayList<Column>();

        for(int i = 1; i <= columnCount; i++) {
            Column column = new Column();
            String columnName = medaData.getColumnName(i);
            int dataType = medaData.getColumnType(i);
            String typeName = medaData.getColumnTypeName(i);
            boolean autoIncrement = medaData.isAutoIncrement(i);

            column.setColumnCode(columnName);
            column.setColumnName(columnName);
            column.setDataType(dataType);
            column.setTypeName(typeName);
            column.setAutoIncrement(autoIncrement);
            columns.add(column);
        }
        return columns;
    }

    /**
     * @param medaData
     * @return List<Column>
     * @throws SQLException
     */
    private static List<Record> getRecords(ResultSet resultSet, int size) throws SQLException {
        int rows = 0;
        List<Record> records = new ArrayList<Record>();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();

        while(resultSet.next()) {
            List<Object> values = new ArrayList<Object>();

            for(int i = 1; i <= columnCount; i++) {
                values.add(resultSet.getObject(i));
            }
            records.add(new Record(values));
            rows++;
            
            if(rows >= size) {
                break;
            }
        }
        return records;
    }

    /**
     * @return int
     */
    private static int getSqlType(String sql) {
        int i = 0;
        int length = sql.length();

        while(i < length && sql.charAt(i) <= ' ') {
            i++;
        }

        int j = i;
        while(j < length && sql.charAt(j) > ' ') {
            j++;
        }

        if(j <= i) {
            return UNKNOWN;
        }

        String word = sql.substring(i, j).toLowerCase();

        if(word.equals("create")) {
            return CREATE;
        }
        else if(word.equals("insert")) {
            return INSERT;
        }
        else if(word.equals("update")) {
            return UPDATE;
        }
        else if(word.equals("delete")) {
            return DELETE;
        }
        else if(word.equals("select")) {
            return SELECT;
        }
        else if(word.equals("alter")) {
            return ALTER;
        }
        else if(word.equals("drop")) {
            return DROP;
        }
        else if(word.equals("show")) {
            return SHOW;
        }
        else {
            return UNKNOWN;
        }
    }
}
